﻿/*******THAY DOI THIET KE BANG tblTon******************/
alter table tblTon add SLKK11OLd float
go
alter table tblTon add SLKK17OLd float
go
/***********THAY DOI THIET KE BANG tblKiemKe****************/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblKiemKe_tblTon]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblKiemKe]'))
ALTER TABLE [dbo].[tblKiemKe] DROP CONSTRAINT [FK_tblKiemKe_tblTon]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblKiemKe]') AND type in (N'U'))
DROP TABLE [dbo].[tblKiemKe]
GO

CREATE TABLE [dbo].[tblKiemKe](
	[ID_KiemKe] [int] IDENTITY(1,1) NOT NULL,
	[DotKiemKe] [int] NULL,
	[ID_MatHang] [int] NULL,
	[SLKKOld] [float] NULL,
	[SLTang] [float] NULL,
	[SLGiam] [float] NULL,
	[SLSS] [float] NULL,
	[GiaKK] [float] NULL,
	[SLKK] [float] NULL,
	[SLThua] [float] NULL,
	[SLThieu] [float] NULL,
	[SLCCL1] [float] NULL,
	[SLCCL2] [float] NULL,
	[SLCCL3a] [float] NULL,
	[SLCCL3b] [float] NULL,
	[SLKhoBo] [float] NULL,
	[SLKhoDV] [float] NULL,
	[SLCSSX] [float] NULL,
	[SLDieuDi] [float] NULL,
	[SLThanhLy] [float] NULL,
	[GhiChu] [nvarchar](100) NULL,
 CONSTRAINT [PK_tblKiemKe] PRIMARY KEY CLUSTERED 
(
	[ID_KiemKe] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1: Đợt 1/1, 2: Đợt 1/7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblKiemKe', @level2type=N'COLUMN',@level2name=N'DotKiemKe'
GO

ALTER TABLE [dbo].[tblKiemKe]  WITH CHECK ADD  CONSTRAINT [FK_tblKiemKe_tblTon] FOREIGN KEY([ID_MatHang])
REFERENCES [dbo].[tblTon] ([ID_MatHang])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblKiemKe] CHECK CONSTRAINT [FK_tblKiemKe_tblTon]
GO
/**************TAO STORE PROCEDURE***************************/
/****************************************************
	Người tạo: Thanh
	Ngày tạo: 4/6/2014
	Nội dung: Thực hiện việc kiểm kê ngày 1/1 tự động	
*****************************************************/
CREATE procedure [dbo].[sp_T_KiemKe11]
as
begin
	delete from tblKiemKe where DotKiemKe =1
	INSERT INTO tblKiemKe
		SELECT 1, ID_MatHang, t.SLKK11Old, SLTang=
			case 
				when t.SLKK11Old < t.SLTonKH then t.SLTonKH-t.SLKK11Old
				else 0 
			end,
			SLGiam =
			case 
				when t.SLKK11Old > t.SLTonKH then t.SLKK11Old-t.SLTonKH
				else 0 
			end,
			t.SLTonKH, t.Gia, t.SLTonKH, 0,0,
			SLCCL1=
			case 
				when t.ID_CCL =1 then t.SLTonKH
				else 0 
			end,
			SLCCL2=
			case 
				when t.ID_CCL =2 then t.SLTonKH
				else 0 
			end,
			SLCCL3a=
			case 
				when t.ID_CCL =3 then t.SLTonKH
				else 0 
			end,
			SLCCL3b=
			case 
				when t.ID_CCL =4 then t.SLTonKH
				else 0 
			end,
			0,0, t.SLTonKH,0,0,null
		FROM tblTon t
end
GO
/****************************************************************/
/****************************************************
	Người tạo: Thanh
	Ngày tạo: 4/6/2014
	Nội dung: Thực hiện việc kiểm kê ngày 1/7 tự động	
*****************************************************/
CREATE procedure [dbo].[sp_T_KiemKe17]
as
begin
	delete from tblKiemKe where DotKiemKe=2
	INSERT INTO tblKiemKe
		SELECT 2, temp.ID_MatHang, temp.SLKK17Old, SLTang=
			case 
				when temp.SLKK17Old < temp.Ton then temp.Ton-temp.SLKK17Old
				else 0 
			end,
			SLGiam =
			case 
				when temp.SLKK17Old > temp.Ton then temp.SLKK17Old -temp.Ton
				else 0 
			end,
			temp.Ton, temp.Gia, temp.Ton, 0,0,
			SLCCL1=
			case 
				when temp.ID_CCL =1 then temp.Ton
				else 0 
			end,
			SLCCL2=
			case 
				when temp.ID_CCL =2 then temp.Ton
				else 0 
			end,
			SLCCL3a=
			case 
				when temp.ID_CCL =3 then temp.Ton
				else 0 
			end,
			SLCCL3b=
			case 
				when temp.ID_CCL =4 then temp.Ton
				else 0 
			end,
			0,0,temp.Ton,0,0,null
		FROM 
		(
				SELECT t.ID_MatHang, t.SLKK17Old, t.Gia, t.ID_CCL, Ton = t.SLSS11 + sum(nx.NhapXuat)
				FROM 
				(
					select ct.ID_MatHang, ct.SLKH as NhapXuat
					from tblSLNX ct, tblDPNX p
					where ct.ID_DPNX= p.ID_DPNX and p.NX='N' and month(p.NgayPhieu)<7
					union all
					select ct.ID_MatHang, -ct.SLKH as NhapXuat
					from tblSLNX ct, tblDPNX p
					where ct.ID_DPNX= p.ID_DPNX and p.NX='X' and month(p.NgayPhieu)<7
				) as nx, tblTon as t
				where nx.ID_MatHang = t.ID_MatHang
				group by t.ID_MatHang, t.SLKK17Old, t.Gia, t.ID_CCL, t.SLSS11
			) as temp
end
GO
/************************************************************************/
CREATE procedure [dbo].[sp_T_LayThongTinKiemKe]
	@dotKK int
as
begin
select kk.*, vt.VatTu, t.MaKyHieu,dv.DVT, t.Gia
from tblKiemKe kk, tblTon t, tblVatTu vt, tblDVT dv
where kk.DotKiemKe = @dotKK and kk.ID_MatHang = t.ID_MatHang and t.ID_VatTu = vt.ID_VatTu and vt.ID_DVT = dv.ID_DVT
end
GO